package com.supermarket.dao;

import com.supermarket.model.ExpiringProduct;
import com.supermarket.model.Product;
import com.supermarket.util.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 临期商品数据访问对象
 * 负责临期商品申报和审批相关的数据库操作
 */
public class ExpiringProductDAO {

    /**
     * 获取所有临期商品申报记录
     * 
     * @return 临期商品申报列表
     */
    public List<ExpiringProduct> getAllExpiringProducts() {
        List<ExpiringProduct> expiringProducts = new ArrayList<>();
        String sql = "SELECT ep.id, ep.product_id, ep.reported_by, ep.current_stock, ep.expiry_date, " +
                "       ep.suggested_action, ep.discount_rate, ep.status, ep.approved_by, " +
                "       ep.report_date, ep.processed_date, ep.remarks, ep.created_at, ep.updated_at, " +
                "       p.product_code, p.product_name, p.unit, p.sale_price, " +
                "       u1.real_name AS reporter_name, u2.real_name AS approver_name " +
                "FROM expiring_products ep " +
                "LEFT JOIN products p ON ep.product_id = p.id " +
                "LEFT JOIN users u1 ON ep.reported_by = u1.id " +
                "LEFT JOIN users u2 ON ep.approved_by = u2.id " +
                "ORDER BY ep.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                ExpiringProduct expiringProduct = createExpiringProductFromResultSet(rs);
                expiringProducts.add(expiringProduct);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return expiringProducts;
    }

    /**
     * 根据状态获取临期商品申报记录
     * 
     * @param status 状态
     * @return 临期商品申报列表
     */
    public List<ExpiringProduct> getExpiringProductsByStatus(String status) {
        List<ExpiringProduct> expiringProducts = new ArrayList<>();
        String sql = "SELECT ep.id, ep.product_id, ep.reported_by, ep.current_stock, ep.expiry_date, " +
                "       ep.suggested_action, ep.discount_rate, ep.status, ep.approved_by, " +
                "       ep.report_date, ep.processed_date, ep.remarks, ep.created_at, ep.updated_at, " +
                "       p.product_code, p.product_name, p.unit, p.sale_price, " +
                "       u1.real_name AS reporter_name, u2.real_name AS approver_name " +
                "FROM expiring_products ep " +
                "LEFT JOIN products p ON ep.product_id = p.id " +
                "LEFT JOIN users u1 ON ep.reported_by = u1.id " +
                "LEFT JOIN users u2 ON ep.approved_by = u2.id " +
                "WHERE ep.status = ? " +
                "ORDER BY ep.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            rs = ps.executeQuery();

            while (rs.next()) {
                ExpiringProduct expiringProduct = createExpiringProductFromResultSet(rs);
                expiringProducts.add(expiringProduct);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return expiringProducts;
    }

    /**
     * 审批临期商品申报
     * 
     * @param id         申报ID
     * @param approvedBy 审批人ID
     * @param status     审批结果状态
     * @param remarks    审批备注
     * @return 是否审批成功
     */
    public boolean approveExpiringProduct(int id, int approvedBy, String status, String remarks) {
        String sql = "UPDATE expiring_products " +
                "SET status = ?, approved_by = ?, processed_date = CURDATE(), " +
                "    remarks = CONCAT(IFNULL(remarks, ''), '\\n审批备注: ', ?), " +
                "    updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            ps.setInt(2, approvedBy);
            ps.setString(3, remarks != null ? remarks : "");
            ps.setInt(4, id);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 根据ID获取临期商品申报详情
     * 
     * @param id 申报ID
     * @return 临期商品申报对象
     */
    public ExpiringProduct getExpiringProductById(int id) {
        String sql = "SELECT ep.id, ep.product_id, ep.reported_by, ep.current_stock, ep.expiry_date, " +
                "       ep.suggested_action, ep.discount_rate, ep.status, ep.approved_by, " +
                "       ep.report_date, ep.processed_date, ep.remarks, ep.created_at, ep.updated_at, " +
                "       p.product_code, p.product_name, p.unit, p.sale_price, " +
                "       u1.real_name AS reporter_name, u2.real_name AS approver_name " +
                "FROM expiring_products ep " +
                "LEFT JOIN products p ON ep.product_id = p.id " +
                "LEFT JOIN users u1 ON ep.reported_by = u1.id " +
                "LEFT JOIN users u2 ON ep.approved_by = u2.id " +
                "WHERE ep.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return createExpiringProductFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 检查商品是否已经申报过临期
     * 
     * @param productId 商品ID
     * @return 是否已申报
     */
    public boolean isProductAlreadyReported(int productId) {
        String sql = "SELECT COUNT(*) FROM expiring_products WHERE product_id = ? AND status = 'PENDING'";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return false;
    }

    /**
     * 申报临期商品
     * 
     * @param expiringProduct 临期商品对象
     * @return 是否申报成功
     */
    public boolean declareExpiringProduct(ExpiringProduct expiringProduct) {
        String sql = "INSERT INTO expiring_products (product_id, reported_by, current_stock, expiry_date, " +
                "suggested_action, discount_rate, status, report_date, remarks) " +
                "VALUES (?, ?, ?, ?, ?, ?, 'PENDING', CURDATE(), ?)";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, expiringProduct.getProductId());
            ps.setInt(2, expiringProduct.getReportedBy());
            ps.setBigDecimal(3, expiringProduct.getCurrentStock());
            ps.setDate(4, expiringProduct.getExpiryDate());
            ps.setString(5, expiringProduct.getSuggestedAction());
            ps.setBigDecimal(6, expiringProduct.getDiscountRate());
            ps.setString(7, expiringProduct.getRemarks());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 获取所有商品的临期信息
     * 
     * @return 商品列表
     */
    public List<Product> getAllProductsWithExpiryInfo() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.production_date, " +
                "       CASE WHEN p.shelf_life_days IS NOT NULL AND i.production_date IS NOT NULL " +
                "            THEN DATE_ADD(i.production_date, INTERVAL p.shelf_life_days DAY) " +
                "            ELSE NULL END AS expiry_date, " +
                "       CASE WHEN p.shelf_life_days IS NOT NULL AND i.production_date IS NOT NULL " +
                "            THEN DATEDIFF(DATE_ADD(i.production_date, INTERVAL p.shelf_life_days DAY), CURDATE()) " +
                "            ELSE NULL END AS remaining_days " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "ORDER BY remaining_days ASC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setProductCode(rs.getString("product_code"));
                product.setProductName(rs.getString("product_name"));
                product.setCategoryId(rs.getInt("category_id"));
                product.setCategoryName(rs.getString("category_name"));
                product.setSupplierId(rs.getInt("supplier_id"));
                product.setSupplierName(rs.getString("supplier_name"));
                product.setUnit(rs.getString("unit"));
                product.setPurchasePrice(rs.getBigDecimal("purchase_price"));
                product.setSalePrice(rs.getBigDecimal("sale_price"));
                product.setShelfLifeDays(rs.getObject("shelf_life_days", Integer.class));
                product.setDescription(rs.getString("description"));
                product.setStatus(rs.getString("status"));
                product.setCreatedAt(rs.getTimestamp("created_at"));
                product.setUpdatedAt(rs.getTimestamp("updated_at"));
                product.setCurrentStock(rs.getBigDecimal("current_stock"));
                product.setMinStock(rs.getBigDecimal("min_stock"));
                product.setMaxStock(rs.getBigDecimal("max_stock"));
                product.setProductionDate(rs.getDate("production_date"));

                // 设置到期日期和剩余天数
                product.setExpiryDate(rs.getDate("expiry_date"));
                Integer remainingDays = rs.getObject("remaining_days", Integer.class);
                if (remainingDays != null) {
                    product.setRemainingDays(remainingDays);
                }

                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 从ResultSet创建ExpiringProduct对象
     */
    private ExpiringProduct createExpiringProductFromResultSet(ResultSet rs) throws SQLException {
        ExpiringProduct expiringProduct = new ExpiringProduct();
        expiringProduct.setId(rs.getInt("id"));
        expiringProduct.setProductId(rs.getInt("product_id"));
        expiringProduct.setReportedBy(rs.getInt("reported_by"));
        expiringProduct.setCurrentStock(rs.getBigDecimal("current_stock"));
        expiringProduct.setExpiryDate(rs.getDate("expiry_date"));
        expiringProduct.setSuggestedAction(rs.getString("suggested_action"));
        expiringProduct.setDiscountRate(rs.getBigDecimal("discount_rate"));
        expiringProduct.setStatus(rs.getString("status"));
        expiringProduct.setApprovedBy(rs.getObject("approved_by", Integer.class));
        expiringProduct.setReportDate(rs.getDate("report_date"));
        expiringProduct.setProcessedDate(rs.getDate("processed_date"));
        expiringProduct.setRemarks(rs.getString("remarks"));
        expiringProduct.setCreatedAt(rs.getTimestamp("created_at"));
        expiringProduct.setUpdatedAt(rs.getTimestamp("updated_at"));

        // 设置关联信息
        expiringProduct.setProductCode(rs.getString("product_code"));
        expiringProduct.setProductName(rs.getString("product_name"));
        expiringProduct.setUnit(rs.getString("unit"));
        expiringProduct.setSalePrice(rs.getBigDecimal("sale_price"));
        expiringProduct.setReporterName(rs.getString("reporter_name"));
        expiringProduct.setApproverName(rs.getString("approver_name"));

        return expiringProduct;
    }
}